package com.qf.mapper;

import com.qf.vo.CategorySellAmts;
import com.qf.vo.DayStatis;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author sunyamei
 * @version V1.0
 * @Project javase2203
 * @Package com.qf.mapper
 * @Description:
 * @Date 2022/7/26 19:22
 *
 * 统计商品分类和对应分类的销售额
 */
public interface IndexTotalMapper {

    @Select("SELECT c.`name` ,sum(o.actual_price) as `value` " +
            "from dts_category c " +
            "left JOIN dts_goods g on c.id=g.category_id " +
            "left join dts_order_goods og on g.id=og.goods_id " +
            "left join dts_order o on og.order_id=o.id " +
            "where o.actual_price is not null  " +
            "group by c.`name`")
    public List<CategorySellAmts> categorySellCount();


    /**
     * 日期和对应的订单总额, 订单笔数
     */
    @Select(" select  " +
            " DATE_FORMAT(o.add_time,\"%Y-%m-%d\") as dayStr, " +
            " count(*) as cnts, " +
            " sum(o.actual_price) as amts " +
            " from dts_order o " +
            " where o.deleted = 0 " +
            " group by DATE_FORMAT(o.add_time,\"%Y-%m-%d\") " +
            " order by dayStr")
    public List<DayStatis> orderAmts();



    /**
     * 统计日期和对应的用户增长量
     * @return
     */
    @Select("select  " +
            " DATE_FORMAT(u.add_time, \"%Y-%m-%d\") as dayStr, " +
            " count(*) as cnts " +
            " from dts_user u " +
            " group by DATE_FORMAT(u.add_time, \"%Y-%m-%d\") " +
            " order BY dayStr")
    public List<DayStatis> userCnts();
}
